USE [WarehouseManagement]
GO
/****** Object:  StoredProcedure [dbo].[InsertProduct]    Script Date: 04/21/2011 10:06:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		duylv00360
-- Create date: 3/14/2011
-- Description:	This stored procedure use to insert a product into database
-- =============================================
ALTER PROCEDURE [dbo].[InsertProduct] 
	-- Add the parameters for the stored procedure here
	@ProductCode varchar(10),
	@ProductName nvarchar(50),
	@Description nvarchar(MAX) = NULL
	, @BuiltupStrutures NVARCHAR(MAX) = NULL
	, @ProductID int = 0 OUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
    -- Insert statements for procedure here
	INSERT INTO Product
	(
		ProductCode
		, ProductName
		, Description
	) 
	VALUES
	(
		@ProductCode
		, @ProductName
		, @Description
	);
	
	SELECT @ProductID = IDENT_CURRENT('Product') -- AS ProductID

	IF(EXISTS (SELECT 1 FROM Product WHERE ProductID = @ProductID AND ProductCode = @ProductCode))
	BEGIN
		DECLARE @XmlPointer INT
	 
		EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @BuiltupStrutures

		INSERT INTO BuiltupStructure(ProductID, MaterialID, MaterialQuantity)
		SELECT 
				@ProductID
				, Data.MaterialID
				, Data.Quantity
		FROM OpenXml(@XmlPointer, 'Data/Item', 2)
		WITH (MaterialID INT, Quantity INT) AS Data
		 
		INNER JOIN
				Material m
		ON 
				m.MaterialID = Data.MaterialID
				
		EXEC sp_xml_removedocument @XmlPointer
	END
	ELSE
	BEGIN
		SELECT @ProductID = -1
	END
END

